import pymysql
import pandas as pd
import plotly.graph_objs as go


def get_conn():
    # 建立连接
    conn = pymysql.connect(host="localhost", user="root",
                           password="123456", db="bs", charset="utf8")
    # c创建游标A
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()


def query(sql, *args):
    """
    :param sql:
    :param args:
    :return:
    """
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res



def get_all_data():
    sql = """SELECT * from DATA """
    res = query(sql)
    columns = ['Ccode','Cshares','code','name','price','Iad','Uadr','YTD','Turnover','money','Trate','dividend_yield','market_value']
    df = pd.DataFrame(list(res), columns=columns)
    return df.to_dict('records')


def get_l1_data():
    sql = """SELECT price,NAME FROM DATA """
    # 当前价格和股票名称
    res = query(sql)
    return res


def get_l2_data():
    sql = """SELECT NAME,Iad FROM DATA  """
    # 股票名和涨跌额
    res = query(sql)
    return res


def get_l3_data():
    sql = """SELECT Cshares, AVG(market_value) AS avg_market_value
             FROM data
             GROUP BY Cshares;"""
    # 各行业市值的平均值
    res = query(sql)
    return res


def get_r1_data():
    sql = """ SELECT Cshares,SUM(c1) AS 'c1',SUM(c2) AS 'c2' FROM(SELECT Cshares,COUNT(CASE WHEN Iad>0 THEN Iad ELSE NULL END)
    AS c1,''AS c2 FROM DATA GROUP BY Cshares
        UNION ALL
        SELECT Cshares,''AS c1,COUNT(*)AS c2 FROM DATA GROUP BY Cshares ) test
        GROUP BY test.Cshares ORDER BY test.Cshares DESC 
        """
    # 各个行业的涨跌情况
    res = query(sql)
    return res


def get_r2_data():
    sql = """SELECT
    Cshares AS industry,
    COUNT(CASE WHEN Iad > 0 THEN 1 END) * 1.0 / (SELECT COUNT(*) FROM data WHERE Iad > 0) 
    AS increase_ratio
FROM
    data
WHERE
    Iad > 0
GROUP BY
    Cshares 
    """
    # 每个行业的股票上涨的分别占所有上涨股票的比值
    res = query(sql)
    data = []
    for row in res:
        data.append((row[0], row[1]))
    return data

def get_r3_data():
    sql = """ SELECT word, count FROM word"""
    # 各个行业的涨跌情况
    res = query(sql)
    return res



# 从 bs 数据库中获取基于LSTM算法所得数据
def get_LSTM_data():
    sql="SELECT table_name, stock_status, stock_change FROM predictions"
    res=query(sql)
    return res

# 从 history 数据库中获取历史股票信息
def get_historical_stock_info(conn, stock_name):
    query = "SELECT * FROM `{}`".format(stock_name)
    df = pd.read_sql(query, conn)
    # 将 date 字段转换为日期时间类型
    df['date'] = pd.to_datetime(df['date'], errors='coerce')
    # 检查是否存在缺失或无效的日期时间值
    null_count = df['date'].isnull().sum()
    if null_count > 0:
        print('发现 {} 条缺失或无效的日期时间值'.format(null_count))
    return df.to_dict('records')


# 使用 plotly 绘制股票历史信息
def plot_stock_history(stock_history):
    df = pd.DataFrame(stock_history)
    fig = go.Figure(data=[go.Candlestick(x=df['date'],
                                         open=df['open'],
                                         high=df['high'],
                                         low=df['low'],
                                         close=df['close'])])
    # 添加均线图
    df['MA5'] = df['close'].rolling(window=5, min_periods=1).mean()
    df['MA10'] = df['close'].rolling(window=10, min_periods=1).mean()
    df['MA20'] = df['close'].rolling(window=20, min_periods=1).mean()
    df['MA30'] = df['close'].rolling(window=30, min_periods=1).mean()
    fig.add_trace(go.Scatter(x=df['date'], y=df['MA5'], name='MA5'))
    fig.add_trace(go.Scatter(x=df['date'], y=df['MA10'], name='MA10'))
    fig.add_trace(go.Scatter(x=df['date'], y=df['MA20'], name='MA20'))
    fig.add_trace(go.Scatter(x=df['date'], y=df['MA30'], name='MA30'))

    fig.update_layout(title='股票历史信息', xaxis_rangeslider_visible=True)
    fig.update_layout(title='股票历史信息',
                      xaxis_title='日期',
                      yaxis_title='价格')
    return fig.to_html(full_html=False)
# 生成Bollinger Bands图


def plot_bollinger_bands(stock_history):
    df = pd.DataFrame(stock_history)
    df['MA20'] = df['close'].rolling(window=20).mean()
    df['std'] = df['close'].rolling(window=20).std()
    df['upper_band'] = df['MA20'] + 2 * df['std']
    df['lower_band'] = df['MA20'] - 2 * df['std']
    fig = go.Figure()
    fig.add_trace(go.Scatter(x=df['date'], y=df['close'], name='收盘价'))
    fig.add_trace(go.Scatter(x=df['date'], y=df['MA20'], name='20日移动平均线'))
    fig.add_trace(go.Scatter(x=df['date'], y=df['upper_band'], name='上轨'))
    fig.add_trace(go.Scatter(x=df['date'], y=df['lower_band'], name='下轨'))
    fig.update_layout(title='Bollinger Bands图',
                      xaxis_title='日期',
                      yaxis_title='价格', xaxis_rangeslider_visible=True)
    return fig.to_html(full_html=False)
# 生成成交量图


def plot_volume_bar(stock_history):
    df = pd.DataFrame(stock_history)
    fig = go.Figure()
    fig.add_trace(go.Bar(x=df['date'], y=df['volume'], name='成交量'))
    fig.update_layout(title='成交量柱状图',
                      xaxis_title='日期',
                      yaxis_title='成交量', xaxis_rangeslider_visible=True)
    return fig.to_html(full_html=False)
# 生成MACD图


def plot_macd(stock_history):
    df = pd.DataFrame(stock_history)
    df['EMA12'] = df['close'].ewm(span=12, adjust=False).mean()
    df['EMA26'] = df['close'].ewm(span=26, adjust=False).mean()
    df['DIF'] = df['EMA12'] - df['EMA26']
    df['DEA'] = df['DIF'].ewm(span=9, adjust=False).mean()
    df['MACD'] = (df['DIF'] - df['DEA']) * 2
    fig = go.Figure()
    fig.add_trace(go.Scatter(x=df['date'], y=df['DIF'], name='DIF'))
    fig.add_trace(go.Scatter(x=df['date'], y=df['DEA'], name='DEA'))
    fig.add_trace(go.Bar(x=df['date'], y=df['MACD'], name='MACD'))
    fig.update_layout(title='MACD图',
                      xaxis_title='日期',
                      yaxis_title='价格', xaxis_rangeslider_visible=True)
    return fig.to_html(full_html=False)
# 生成RSI图的函数


def plot_rsi(stock_history):
    df = pd.DataFrame(stock_history)
    df['change'] = df['close'] - df['close'].shift(1)
    df['gain'] = df['change'].apply(lambda x: x if x > 0 else 0)
    df['loss'] = df['change'].apply(lambda x: abs(x) if x < 0 else 0)
    df['avg_gain'] = df['gain'].rolling(window=14).mean()
    df['avg_loss'] = df['loss'].rolling(window=14).mean()
    df['RS'] = df['avg_gain'] / df['avg_loss']
    df['RSI'] = 100 - (100 / (1 + df['RS']))
    fig = go.Figure()
    fig.add_trace(go.Scatter(x=df['date'], y=df['RSI'], name='RSI'))
    fig.add_shape(type='line', x0=df['date'].min(), y0=30, x1=df['date'].max(), y1=30,
                  line=dict(color='red', width=1, dash='dash'))
    fig.add_shape(type='line', x0=df['date'].min(), y0=70, x1=df['date'].max(), y1=70,
                  line=dict(color='red', width=1, dash='dash'))
    fig.update_layout(title='RSI图',
                      xaxis_title='日期',
                      yaxis_title='RSI', xaxis_rangeslider_visible=True)
    return fig.to_html(full_html=False)
# 散点图


def plot_scatter(stock_history):
    df = pd.DataFrame(stock_history)
    fig = go.Figure()
    fig.add_trace(go.Scatter(
        x=df['open'], y=df['close'], mode='markers', name='开盘价-收盘价'))
    fig.update_layout(title='散点图',
                      xaxis_title='开盘价',
                      yaxis_title='收盘价')
    return fig.to_html(full_html=False)


if __name__ == "__main__":
    print(get_l2_data())
